package com.iehyou.dao.db;

import org.apache.commons.lang.StringUtils;

public class DbmsDialectJdbc {
	private static String dbms = "oracle";

	public static String getDbms() {
		return dbms;
	}

	public static void setDbms(String dbms) {
		DbmsDialectJdbc.dbms = dbms;
	}
	/**
	 * 分页查询,适用mysql和oracle
	 * @param sql
	 * @param startId
	 * @param endId
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public static String get_slice(String sql, int startId, int endId) throws UnsupportedOperationException {
		String limitSqlStr = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			if (startId > 0) {
				limitSqlStr = sql + " limit " + (startId - 1) + "," + (endId - startId + 1);
			} else {
				limitSqlStr = sql + " limit " + endId;
			}
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			if (startId > 0) {
				throw new UnsupportedOperationException("sql server has no offset");
			} else {
				String newsql = sql.toUpperCase();
				int sql_orderby = newsql.lastIndexOf(" ORDER ");
				int sql_orderby2 = newsql.indexOf(" BY ");
				if (sql_orderby > 0 && sql_orderby2 > 0) {
					sql = sql.substring(0, sql_orderby);
				}
				limitSqlStr = "select top " + endId + " * from (" + sql + ") as newTab ";
			}
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			if (startId > 0) {
				limitSqlStr = "select * from   (select newtable.*,rownum rnum from    (" + sql + ") newtable )   where rnum<= " + endId + " and rnum > " + startId;
			} else {
				limitSqlStr = "select * from (" + sql + " ) where rownum > 0 and rownum <= " + endId;
			}
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix has no offset");
			
		}
		return limitSqlStr;
	}
	/**
	 * 获取数据库时间,适用mysql和oracle,informix,mssql
	 * @param sql
	 * @param startId
	 * @param endId
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String getCurrentDateString() throws UnsupportedOperationException {
		String currentDate = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			currentDate = "CURRDATE";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			currentDate = "sysdate";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			currentDate = "sysdate";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			currentDate = "getdate()";
		}
		return currentDate;
	}
	/**
	 * 适用数据库函数格式化日期
	 * @param date 格式 yyyy/mm/dd
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String getFormateDateString(String date) throws UnsupportedOperationException {
		String formatDate = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			formatDate = "date(" + date + ")";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			formatDate = " to_date('" + date + "','yyyy/mm/dd')";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			formatDate = date;
		}
		return formatDate;
	}
	
	/**
	 * 返回sql语句的总记录数SQL
	 * @param sql
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String getCountSql(String sql) throws UnsupportedOperationException {
		String countStr = "";
		if ("informix".equalsIgnoreCase(dbms)) {
			String tempSql = sql.toUpperCase();
			int fromCount = StringUtils.countMatches(tempSql, " FROM");
			int sql_from = tempSql.indexOf(" FROM");
			if (fromCount > 1 && tempSql.indexOf("(SELECT") < tempSql.indexOf(" FROM")) {
				sql_from = tempSql.lastIndexOf(" FROM");
			}
			int sql_orderby = tempSql.indexOf(" ORDER ");
			int sql_groupby = tempSql.indexOf(" GROUP ");
			if (sql_orderby != -1)
				sql_orderby++;
			if (sql_groupby != -1)
				sql_groupby++;
			if (sql_orderby > 0) {
				countStr = "select count(1) " + sql.substring(sql_from, sql_orderby);
			} else if (sql_groupby > 0) {
				countStr = "select count(1) " + sql.substring(sql_from, sql_groupby);
			} else {
				countStr = "select count(1) " + sql.substring(sql_from);
			}
			return countStr;
		}
		if ("mysql".equalsIgnoreCase(dbms)) {
			return "select count(1) from (" + sql + ") as eastcomtab";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			String newsql = sql.toUpperCase();
			int sql_orderby = newsql.lastIndexOf(" ORDER ");
			int sql_orderby2 = newsql.indexOf(" BY ");
			if (sql_orderby > 0 && sql_orderby2 > 0) {
				sql = sql.substring(0, sql_orderby);
			}
			return "select count(1) from (" + sql + ") as newtab";
		} else {
			return "select count(1) from (" + sql + ") ";
		}
	}

	public String getSubConditionSql(String sql) {
		String countStr = "";
		if ("informix".equalsIgnoreCase(dbms)) {
			String tempSql = sql.toUpperCase();
			int fromCount = StringUtils.countMatches(tempSql, " FROM");
			int sql_from = tempSql.indexOf(" FROM");
			if (fromCount > 1 && tempSql.indexOf("(SELECT") < tempSql.indexOf(" FROM")) {
				sql_from = tempSql.lastIndexOf(" FROM");
			}
			int sql_orderby = tempSql.indexOf(" ORDER ");
			int sql_groupby = tempSql.indexOf(" GROUP ");
			if (sql_orderby != -1)
				sql_orderby++;
			if (sql_groupby != -1)
				sql_groupby++;
			if (sql_orderby > 0) {
				countStr = "select * " + sql.substring(sql_from, sql_orderby);
			} else if (sql_groupby > 0) {
				countStr = "select * " + sql.substring(sql_from, sql_groupby);
			} else {
				countStr = "select * " + sql.substring(sql_from);
			}
			return countStr;
		}
		if ("mysql".equalsIgnoreCase(dbms)) {
			return "select * from (" + sql + ") as eastcomtab";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			String newsql = sql.toUpperCase();
			int sql_orderby = newsql.indexOf("ORDER");
			if (sql_orderby > 0) {
				sql = sql.substring(0, sql_orderby);
			}
			return sql;
		} else {
			return sql;
		}
	}

	/**
	 * 返回数据库去左右空格sql,支持mssql,oracle
	 * @param sql
	 * @return
	 */
	public String getTrimSql(String sql) {
		String recursionSqlStr = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException(" unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			recursionSqlStr = "rtrim(ltrim(" + sql + "))";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			recursionSqlStr = "trim(" + sql + ")";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return recursionSqlStr;
	}

	

	

	/**
	 * 返回字符串连接函数sql,支持mssql,oracle
	 * @param sqlList
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String concat(java.util.List<?> sqlList) throws UnsupportedOperationException {
		String sql = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException(" unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			for (int i = 0; i < sqlList.size(); i++) {
				sql = sql + "+" + sqlList.get(i);
			}
			if (StringUtils.isNotEmpty(sql))
				return sql.substring(1);
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			for (int i = 0; i < sqlList.size(); i++) {
				sql = sql + "||" + sqlList.get(i);
			}
			if (StringUtils.isNotEmpty(sql))
				return sql.substring(2);
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return sql;
	}

	/**
	 * 传入sql替换成数据库连接字符串函数.
	 * @param sql
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String concat(String sql) throws UnsupportedOperationException {
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException(" unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return StringUtils.replace(sql, "||", "+");
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return StringUtils.replace(sql, "+", "||");
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return sql;
	}

	/**
	 * 返回数据库substring的实现
	 * @param sql
	 * @param start
	 * @param length
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String substring(String sql, int start, int length) throws UnsupportedOperationException {
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException(" unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return "substring(" + sql + "," + start + "," + length + ")";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return "substr(" + sql + "," + start + "," + length + ")";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return sql;
	}
	
	/**
	 * 添加条件为null判断
	 * @param fieldName
	 * @return
	 */
	public String getIsNullSql(String fieldName) {
		String isnullStr = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			isnullStr = fieldName + " = NULL ";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			isnullStr = fieldName + " = '' OR " + fieldName + " IS NULL ";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			isnullStr = fieldName + " IS NULL ";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			isnullStr = fieldName + " = NULL ";
		}
		return isnullStr;
	}

	/**
	 * 返回空函数SQ;
	 * @param fieldName
	 * @return
	 */
	public String isNull(String fieldName, String defaultValue) throws UnsupportedOperationException {
		String isnullStr = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return "isNull(" + fieldName + "," + defaultValue + ")";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return "nvl(" + fieldName + "," + defaultValue + ")";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return isnullStr;
	}

	
	/**
	 * 返回空函数名
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String isNull() throws UnsupportedOperationException {
		String isnullStr = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			return "ifNull";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return "isNull";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return "Nvl";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return isnullStr;
	}

	/**
	 * 日期计算
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String dateDiff(String sql) {
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return "datediff(minute," + sql + ",getdate())";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return "(sysdate-" + sql + ")*24*60";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return sql;
	}

	public String leftJoinSql(String tableName, String asStr, String conStr) {
		String leftJoinSql = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return " left outer join  " + tableName + " " + asStr + " on  " + conStr + " WHERE ";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return " , " + tableName + " " + asStr + " Where " + conStr + "(+) and ";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return leftJoinSql;
	}

	public String getFormateDateString(String date, String pattern) throws UnsupportedOperationException {
		String formatDate = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			formatDate = "date('" + date + "')";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			formatDate = " to_date('" + date + "','" + pattern + "')";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			if ("yyyy-mm-dd hh24:mi:ss".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar, " + date + " , 120 )";
			} else if ("yyyy-mm-dd".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 23 )";
			} else if ("yyyy/mm/dd".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 111 )";
			} else if ("yyyymmdd".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 112 )";
			} else if ("yyyy/mm".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 111 )";
			} else if ("yyyy.mm.dd".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 102 )";
			} else if ("dd/mm/yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 103 )";
			} else if ("mm/dd/yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 101 )";
			} else if ("dd.mm.yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 104 )";
			} else if ("dd-mm-yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 105 )";
			} else if ("dd mm yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 106 )";
			} else if ("dd mm, yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 107 )";
			} else if ("HH24:mi:ss".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 108 )";
			} else if ("mm dd yyyy ss".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 109 )";
			} else if ("mm-dd-yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 110 )";
			} else if ("mm-dd-yyyy".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 113 )";
			} else if ("HH24:mi:ss.sss".equalsIgnoreCase(pattern)) {
				formatDate = "CONVERT(varchar(12) , " + date + " , 114 )";
			} else {
				formatDate = date;
			}
		}
		return formatDate;
	}

	public String getFormateNumberString(String number, String pattern) throws UnsupportedOperationException {
		String formatNumber = "";
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("mysql unsupport recursion SQL query");
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			formatNumber = " to_char(" + number + ",'" + pattern + "')";
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			formatNumber = " convert(varchar(16)," + number + "," + pattern + ")";
		}
		return formatNumber;
	}

	/**
	 * 给fieldname添加函数,返回sql.
	 * @param fieldname
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String length(String fieldname) throws UnsupportedOperationException {
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			return "len(" + fieldname + ")";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			return "length(" + fieldname + ")";
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return fieldname;
	}

	/**
	 * 增减指定日期
	 * 
	 * @param fieldname
	 * @param inteval
	 * @param datepart
	 * @return
	 * @throws UnsupportedOperationException
	 */
	public String dateAdd(String fieldname, String inteval, String datepart) throws UnsupportedOperationException {
		if ("mysql".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		} else if ("mssql".equalsIgnoreCase(dbms)) {
			fieldname = "dateAdd(" + datepart + ", " + inteval + ", " + fieldname + ")";
		} else if ("oracle".equalsIgnoreCase(dbms)) {
			if ("mm".equals(datepart)) {
				fieldname = "interval " + inteval + " day ";
			}
		} else if ("informix".equalsIgnoreCase(dbms)) {
			throw new UnsupportedOperationException("informix unsupport recursion SQL query");
		}
		return fieldname;
	}

	public String getSumFieldsSql(String sql, String[] fileds, String[] attributes) {
		StringBuffer buffer = new StringBuffer("");
		StringBuffer bufRet = new StringBuffer("");

		for (int i = 0, len = fileds.length; i < len; i++) {
			buffer.append(" sum(" + fileds[i] + ") AS " + attributes[i] + ",");
		}
		int sLen = buffer.length();
		if (sLen > 0) {
			buffer = buffer.deleteCharAt(sLen - 1);
		}
		if ("informix".equalsIgnoreCase(dbms)) {
			String tempSql = sql.toUpperCase();
			int fromCount = StringUtils.countMatches(tempSql, " FROM");
			int sql_from = tempSql.indexOf(" FROM");
			if (fromCount > 1 && tempSql.indexOf("(SELECT") < tempSql.indexOf(" FROM")) {
				sql_from = tempSql.lastIndexOf(" FROM");
			}
			int sql_orderby = tempSql.indexOf(" ORDER ");
			int sql_groupby = tempSql.indexOf(" GROUP ");
			if (sql_orderby != -1)
				sql_orderby++;
			if (sql_groupby != -1)
				sql_groupby++;
			if (sql_orderby > 0) {
				bufRet.append("select " + buffer + sql.substring(sql_from, sql_orderby));
			} else if (sql_groupby > 0) {
				bufRet.append("select " + buffer + sql.substring(sql_from, sql_groupby));
			} else {
				bufRet.append("select " + buffer + sql.substring(sql_from));
			}
			return bufRet.toString();
		}
		if ("mysql".equalsIgnoreCase(dbms)) {
			bufRet.append("select " + buffer + " from (" + sql + ") as biyatab");
			return bufRet.toString();
		}
		if ("mssql".equalsIgnoreCase(dbms)) {
			String newsql = sql.toUpperCase();
			int sql_orderby = newsql.lastIndexOf(" ORDER ");
			int sql_orderby2 = newsql.indexOf(" BY ");
			if (sql_orderby > 0 && sql_orderby2 > 0) {
				sql = sql.substring(0, sql_orderby);
			}
			bufRet.append("select " + buffer + " from (" + sql + ") as biyatab");
			return bufRet.toString();
		} else {
			bufRet.append("select " + buffer + " from (" + sql + ") ");
			return bufRet.toString();
		}
	}

}
